﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.EntityClient;
using System.Data.Objects;
using System.Data.Objects.DataClasses;
using System.Linq;
using System.Text;
using EntityFrameworkSampleCode.TablePerHierarchy;
using EntityFrameworkSampleCode.TablePerType;
using EntityFrameworkSampleCode.TablePerClass;

using TPHVehicle = EntityFrameworkSampleCode.TablePerHierarchy.Vehicle;
using TPHCar = EntityFrameworkSampleCode.TablePerHierarchy.Car;
using TPHBoat = EntityFrameworkSampleCode.TablePerHierarchy.Boat;
using TPTVehicle = EntityFrameworkSampleCode.TablePerType.Vehicle;
using TPTCar = EntityFrameworkSampleCode.TablePerType.Car;
using TPTBoat = EntityFrameworkSampleCode.TablePerType.Boat;

namespace Chapter6Classes
{
	public class Chapter6Worker
	{
		private const String CarPrintPattern = "This is {0} car with tiresize {1}.";
		private const String BoatPrintPattern = "This is {0} boat with propellersize {1}.";

		public static void Work()
		{
			var context = new NorthwindEntities();

			#region Northwind db related practice methods

			//EagerLoadingTest1(category);
			//QueryTest(context);
			//QuantityChangingTest(context);
			//ModelDefinedFunctionTest(context);
			
			//ESQLEntityCommandTest(context);
			//ESQLObjectQueryTest(context);
			//ESQLRowQueryTest(context);
			ESQLMultisetTest(context);

			#endregion
			 
			#region Vehicles db related methods

			//using(var tphContext = new VehiclesEntities())
			//{
			//    var vehicles = tphContext.Vehicles.ToList();
			//    QueryVehicles<TPHCar, TPHVehicle>(vehicles);
			//    QueryVehicles<TPHBoat, TPHVehicle>(vehicles);
			//}

			//using (var tptContext = new VehiclesTPTEntities())
			//{
			//    var vehicles = tptContext.Vehicles.ToList();
			//    QueryVehicles<TPTCar, TPTVehicle>(vehicles);
			//    QueryVehicles<TPTBoat, TPTVehicle>(vehicles);
			//}

			//using (var tpcContext = new VehiclesTPCEntities())
			//{
			//    var boats = tpcContext.Boats.Where(b => b.Make.StartsWith("S"));
			//    foreach (var boat in boats)
			//    {
			//        PrintVehicle(BoatPrintPattern, boat.Model, boat.PropSize);
			//    }
			//}

			#endregion

			#region VehiclesTPT db related methods



			#endregion
		}

		#region Northwind db related methods

		private static void EagerLoadingTest1(NorthwindEntities context)
		{
			var category = context.Categories.Include("Products").FirstOrDefault();
			var products = category.Products;
			Console.WriteLine(category.CategoryID);
			foreach (var product in products)
			{
				Console.WriteLine(product.ProductName);
			}
		}

		private static void StoredProcedureTest()
		{
			var context = new NorthwindEntities();
			//context.CustOrderHist("ALKFI");
		}

		private static void QueryTest(NorthwindEntities context)
		{
			var query = (from od in context.Order_Details.Include("Order").AsEnumerable()
			             where od.Order.CustomerID == "ALFKI"
			             select new
			                    	{
			                    		od.OrderID,
			                    		od.ProductID,
			                    		od.Quantity,
			                    		od.Discount
			                    		//od.DetailTotal
			                    	}).ToList();
			//foreach (var data in query)
			//{
			//    Console.WriteLine(data.DetailTotal);
			//}
		}

		private static void QuantityChangingTest(NorthwindEntities context)
		{
			var orderDetail = context.Order_Details.FirstOrDefault();
			orderDetail.Quantity = 123;
			context.SaveChanges();
		}

		private static void ModelDefinedFunctionTest(NorthwindEntities context)
		{
			var odResult = from od in context.Order_Details
			            select new
			                   	{
			                   		od.OrderID,
									od.ProductID,
									od.Quantity,
									od.Discount,
									od.UnitPrice,
									DetailTotal = NorthwindModelDefinedFunctions.OrderDetailTotal(od)
			                   	};

			foreach (var orderDetails in odResult)
			{
				Console.WriteLine(String.Format("Quantity - {0}; Discount - {1}; UnitPrice - {2}; Total - {3};", orderDetails.Quantity, orderDetails.Discount, orderDetails.UnitPrice, orderDetails.DetailTotal));
			}
		}

		#endregion

		#region Vehicles db related methods

		private static void QueryVehicles<T, P>(IEnumerable<P> collection)
		{
			var query = from c in collection.OfType<T>()
			             select c;

			foreach (var vehicle in query)
			{
				if(vehicle is TPHBoat || vehicle is TPTBoat)
				{
					var tphBoat = vehicle as TPHBoat;
					if(tphBoat != null)
						PrintVehicle(BoatPrintPattern, tphBoat.Model, tphBoat.PropSize);
					else
					{
						var tptBoat = vehicle as TPTBoat;
							PrintVehicle(BoatPrintPattern, tptBoat.Model, tptBoat.PropSize);
					}
				}
				else if(vehicle is TPHCar || vehicle is TPTCar)
				{
					var tphCar = vehicle as TPHCar;
					if (tphCar != null)
						PrintVehicle(CarPrintPattern, tphCar.Model, tphCar.TireSize);
					else
					{
						var tptCar = vehicle as TPTCar;
							PrintVehicle(CarPrintPattern, tptCar.Model, tptCar.TireSize);
					}
				}
			}
		}

		private static void PrintVehicle(String pattern, String model, String partSize)
		{
			Console.WriteLine(String.Format(pattern, model, partSize));
		}

		#endregion

		#region VehiclesTPT db related methods

		//private void QueryVehiclesFromTPT()
		//{
		//    using(var context = new VehiclesTPTEntities())
		//    {
				
		//    }
		//}

		#endregion

		#region ESQLTestMethods

		private static void ESQLEntityCommandTest(NorthwindEntities context)
		{
			using (var entityConnection = new EntityConnection("name=NorthwindEntities"))
			{
				entityConnection.Open();
				const string query = "SELECT c.CustomerID FROM NorthwindEntities.Customers AS c";
				using (var command = new EntityCommand(query, entityConnection))
				{
					var customersIds = new List<String>();
					var reader = command.ExecuteReader(CommandBehavior.SequentialAccess);

					while (reader.Read())
					{
						customersIds.Add((String)reader[0]);
					}

					foreach (var customersId in customersIds)
					{
						Console.WriteLine(customersId);
					}
				}
			}
		}

		private static void ESQLObjectQueryTest(NorthwindEntities context)
		{
			context.Connection.Open();
			const string query = "SELECT c.CustomerID FROM NorthwindEntities.Customers AS c";
			var customerIds = new ObjectQuery<DbDataRecord>(query, context).ToList();

			foreach (var dbDataReader in customerIds.Select(c => new { ID = c.GetString(0) }).ToList())
			{
				Console.WriteLine(dbDataReader);
			}
			//using (var entityConnection = new EntityConnection("name=NorthwindEntities"))
			//{

			//}
		}

		private static void ESQLRowQueryTest(NorthwindEntities context)
		{
			context.Connection.Open();
			const String query = "ROW(1 AS MyIndex, 'ALFKI' AS MyId);";
			var myStuff = new ObjectQuery<DbDataRecord>(query, context).ToList();

			foreach (var dbDataRecord in myStuff.ToList())
			{
				Console.WriteLine(String.Format("Index - {0}; Id - {1};", dbDataRecord.GetInt32(0), dbDataRecord.GetString(1)));
			}
		}

		private static void ESQLMultisetTest(NorthwindEntities context)
		{
			context.Connection.Open();
			string query = "SELECT i FROM {1,2,3,4,5} AS i";
			var myStuff = new ObjectQuery<DbDataRecord>(query, context);

			foreach (var ms in myStuff.ToList().Select(i => new { Index = i.GetInt32(0) }))
			{
				Console.WriteLine(ms);
			}
		}

		#endregion
	}
}
